﻿using Infrastructures;
using IRepository;
using Models;
using Spring.Data.Common;
using Spring.Data.Generic;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace RepositoryMySQL
{
    public class AssociationRepository : IAssociationRepository
    {
        private string baseAssociation = "select AssociationNO,Name,SchoolNO,AssociationGrade,ScaleNO,DeptAmount,Status,Remark,NatureNO,TypeNO,TypeDetailNO,SchoolType,SelfPassword,UserPassword,CreateUserNO,UpdateTime,CreateTime,LogoPath,ChargeName,SchoolAddress from Association";
        public Association Create(Association obj)
        {
            string strIns = @"insert into Association(Name,SchoolNO,AssociationGrade,ScaleNO,DeptAmount,Status,Remark,NatureNO,TypeNO,TypeDetailNO,SchoolType,SelfPassword,UserPassword,CreateUserNO,UpdateTime,CreateTime,LogoPath,ChargeName,SchoolAddress) values(@Name,@SchoolNO,@AssociationGrade,@ScaleNO,@DeptAmount,@Status,@Remark,@NatureNO,@TypeNO,@TypeDetailNO,@SchoolType,@SelfPassword,@UserPassword,@CreateUserNO,@UpdateTime,@CreateTime,@LogoPath,@ChargeName,@SchoolAddress);SELECT LAST_INSERT_ID() as id";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();

            p.Add("AssociationNO", DbType.Int32).Value = obj.AssociationNO;
            p.Add("Name", DbType.String).Value = obj.Name;
            p.Add("SchoolNO", DbType.Int32).Value = obj.SchoolNO;
            p.Add("AssociationGrade", DbType.String).Value = obj.AssociationGrade;
            p.Add("ScaleNO", DbType.Int32).Value = obj.ScaleNO;
            p.Add("DeptAmount", DbType.Int32).Value = obj.DeptAmount;
            p.Add("Status", DbType.String).Value = obj.Status;
            p.Add("Remark", DbType.String).Value = obj.Remark;
            p.Add("NatureNO", DbType.Int32).Value = obj.NatureNO;
            p.Add("TypeNO", DbType.Int32).Value = obj.TypeNO;
            p.Add("TypeDetailNO", DbType.Int32).Value = obj.TypeDetailNO;
            p.Add("SchoolType", DbType.String).Value = obj.SchoolType;
            p.Add("SelfPassword", DbType.String).Value = obj.SelfPassword;
            p.Add("UserPassword", DbType.String).Value = obj.UserPassword;
            p.Add("CreateUserNO", DbType.Int32).Value = obj.CreateUserNO;
            p.Add("UpdateTime", DbType.DateTime).Value = obj.UpdateTime;
            p.Add("CreateTime", DbType.DateTime).Value = obj.CreateTime;
            p.Add("LogoPath", DbType.String).Value = obj.LogoPath;
            p.Add("ChargeName", DbType.String).Value = obj.ChargeName;
            p.Add("SchoolAddress", DbType.String).Value = obj.SchoolAddress;

           

            int num = Int32.Parse(DBHelper.TemplateMySQL.ExecuteScalar(CommandType.Text, strIns, p).ToString());
            obj.AssociationNO = num;
            return obj;
        }
        public void Update(Association obj)
        {
            string strUpdate = @"update Association set Name=@Name,SchoolNO=@SchoolNO,AssociationGrade=@AssociationGrade,ScaleNO=@ScaleNO,DeptAmount=@DeptAmount,Status=@Status,Remark=@Remark,NatureNO=@NatureNO,TypeNO=@TypeNO,TypeDetailNO=@TypeDetailNO,SchoolType=@SchoolType,SelfPassword=@SelfPassword,UserPassword=@UserPassword,CreateUserNO=@CreateUserNO,UpdateTime=@UpdateTime,CreateTime=@CreateTime, LogoPath=@LogoPath,ChargeName=@ChargeName,SchoolAddress=@SchoolAddress where AssociationNO=@AssociationNO";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();

            p.Add("AssociationNO", DbType.Int32).Value = obj.AssociationNO;
            p.Add("Name", DbType.String).Value = obj.Name;
            p.Add("SchoolNO", DbType.Int32).Value = obj.SchoolNO;
            p.Add("AssociationGrade", DbType.String).Value = obj.AssociationGrade;
            p.Add("ScaleNO", DbType.Int32).Value = obj.ScaleNO;
            p.Add("DeptAmount", DbType.Int32).Value = obj.DeptAmount;
            p.Add("Status", DbType.String).Value = obj.Status;
            p.Add("Remark", DbType.String).Value = obj.Remark;
            p.Add("NatureNO", DbType.Int32).Value = obj.NatureNO;
            p.Add("TypeNO", DbType.Int32).Value = obj.TypeNO;
            p.Add("TypeDetailNO", DbType.Int32).Value = obj.TypeDetailNO;
            p.Add("SchoolType", DbType.String).Value = obj.SchoolType;
            p.Add("SelfPassword", DbType.String).Value = obj.SelfPassword;
            p.Add("UserPassword", DbType.String).Value = obj.UserPassword;
            p.Add("CreateUserNO", DbType.Int32).Value = obj.CreateUserNO;
            p.Add("UpdateTime", DbType.DateTime).Value = obj.UpdateTime;
            p.Add("CreateTime", DbType.DateTime).Value = obj.CreateTime;
            p.Add("LogoPath", DbType.String).Value = obj.LogoPath;
            p.Add("ChargeName", DbType.String).Value = obj.ChargeName;
            p.Add("SchoolAddress", DbType.String).Value = obj.SchoolAddress;

            DBHelper.TemplateMySQL.ExecuteNonQuery(CommandType.Text, strUpdate, p);
        }
        public Association Get(string associationno)
        {
            string strSel = baseAssociation + " where AssociationNO=@AssociationNO";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("AssociationNO", DbType.Int32).Value = associationno.ToInt();


            IList<Association> lst = DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, strSel, new RowMapperDelegate<Association>(MapAssociation), p);
            return lst.Count == 0 ? null : lst[0];
        }
        public IList<Association> List()
        {
            string strSel = baseAssociation;

            IList<Association> lst = DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, strSel, new RowMapperDelegate<Association>(MapAssociation));

            return lst;
        }
        public IList<Association> ListAssociation(int schoolno)
        {
            string strSel = baseAssociation + " where SchoolNO=@SchoolNO";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("SchoolNO", DbType.Int32).Value = schoolno;


            IList<Association> lst = DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, strSel, new RowMapperDelegate<Association>(MapAssociation), p);

            return lst;
        }

        public Association GetRelationship(int studnetID, RelationshipFun fun)
        {
            string strSel = baseAssociation + " where AssociationNO=(select NOFrom from Relationship where Fun=@Fun and NOTo=@NOTo)";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("Fun", DbType.String).Value = fun.ToString();
            p.Add("NOTo", DbType.Int32).Value = studnetID;

            IList<Association> lst = DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, strSel, new RowMapperDelegate<Association>(MapAssociation), p);
            return lst.Count == 0 ? null : lst[0];
        }

        private Association MapAssociation(IDataReader dr, int count)
        {
            Association obj = new Association();
            obj.AssociationNO = dr.GetInt32(0);
            obj.Name = dr.IsDBNull(1) ? String.Empty : dr.GetString(1);
            obj.SchoolNO = dr.GetInt32(2);
            obj.AssociationGrade = dr.GetString(3);
            obj.ScaleNO = dr.GetInt32(4);
            obj.DeptAmount = dr.GetInt32(5);
            obj.Status = (CommonStatus)Enum.Parse(typeof(CommonStatus), dr.GetString(6), true);
            obj.Remark = dr.IsDBNull(7) ? String.Empty : dr.GetString(7);
            obj.NatureNO = dr.GetInt32(8);
            obj.TypeNO = dr.GetInt32(9);
            obj.TypeDetailNO = dr.GetInt32(10);
            obj.SchoolType = dr.IsDBNull(11) ? String.Empty : dr.GetString(11);
            obj.SelfPassword = dr.IsDBNull(12) ? String.Empty : dr.GetString(12);
            obj.UserPassword = dr.IsDBNull(13) ? String.Empty : dr.GetString(13);
            obj.CreateUserNO = dr.GetInt32(14);
            obj.UpdateTime = dr.GetDateTime(15);
            obj.CreateTime = dr.GetDateTime(16);
            obj.LogoPath = dr.IsDBNull(17) ? String.Empty : dr.GetString(17);
            obj.ChargeName = dr.IsDBNull(18) ? String.Empty : dr.GetString(18);
            obj.SchoolAddress = dr.IsDBNull(19) ? String.Empty : dr.GetString(19);

            return obj;
        }

    }
}
